﻿
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration.Json;
using Microsoft.Extensions.Configuration;
using MySql.Data.MySqlClient;
using System.Data.Common;
using Dapper;
using System.Linq.Expressions;
using DbFactory.Base;

namespace DbFactory
{
    public class SQLServerConnection : IDatabaseConnection
    {
        private SqlConnection _connection;

        public void connect()
        {
            IConfiguration config = new ConfigurationBuilder()
            .Add(new JsonConfigurationSource { Path = "app.json", ReloadOnChange = true })
            .Build();
            string connectionString = config.GetSection("connectionStr").Value;
            _connection = new SqlConnection(connectionString);
            _connection.Open();
        }
        public void connect(string connectionString)
        {
            IConfiguration config = new ConfigurationBuilder()
            .Add(new JsonConfigurationSource { Path = "app.json", ReloadOnChange = true })
            .Build();
            _connection = new SqlConnection(connectionString);
            _connection.Open();
        }

        public void disconnect()
        {
            _connection.Close();
            _connection.Dispose();
        }


        public DbTransaction BeginTransaction()
        {
            return _connection.BeginTransaction();
        }

        public void insert<T>(T entity, DbTransaction transaction = null)
        {
            var type = typeof(T);
            var properties = type.GetProperties()
                .Where(p => p.CanWrite && p.PropertyType.IsPublic)
                .ToList();
            var columnNames = string.Join(", ", properties.Select(p => p.Name));
            var parameterNames = string.Join(", @", properties.Select(p => $"@{p.Name}"));
            var sql = $"INSERT INTO {type.Name} ({columnNames}) VALUES ({parameterNames})";
            var parameters = new DynamicParameters();
            foreach (var property in properties)
            {
                parameters.Add("@" + property.Name, property.GetValue(entity, null));
            }
            if (transaction != null)
            {
                _connection.Execute(sql, parameters, transaction);
            }
            else
            {
                _connection.Execute(sql, parameters);
            }
        }


        public void update<T>(T entity, DbTransaction transaction = null, string key = "Id")
        {
            var type = typeof(T);
            var properties = type.GetProperties()
                .Where(p => p.CanRead && p.PropertyType.IsPublic)
                .ToList();

            var setClause = string.Join(", ", properties
                .Where(p => !p.Name.Equals("Id", StringComparison.OrdinalIgnoreCase))
                .Select(p => $"{p.Name} = @{p.Name}"));
            var whereClause = key + " = @" + key;
            var sql = $"UPDATE {type.Name} SET {setClause} WHERE {whereClause}";

            var parameters = new DynamicParameters();
            foreach (var property in properties)
            {
                parameters.Add("@" + property.Name, property.GetValue(entity, null));
            }
            if (transaction != null)
            {
                _connection.Execute(sql, parameters, transaction);
            }
            else
            {
                _connection.Execute(sql, parameters);
            }
        }



        public void delete<T>(string id , DbTransaction transaction = null)
        {
            string keyName = "id";
            var t = typeof(T);
            string sql = string.Format("delete from {0} where {1}={2}", t.Name, keyName, id);
            if (transaction != null)
            {
                _connection.Execute(sql, transaction);
            }
            else
            {
                _connection.Execute(sql);
            }

        }



        public void delete<T>(string id, string keyName = "id", DbTransaction transaction = null)
        {
            var t = typeof(T);
            string sql = string.Format("delete from {0} where {1}={2}", t.Name, keyName, id);
            if (transaction != null)
            {
                _connection.Execute(sql, transaction);
            }
            else
            {
                _connection.Execute(sql);
            }

        }

        public void delete<T>(string id)
        {
            string keyName = "id";
            var t = typeof(T);
            string sql = string.Format("delete from {0} where {1}={2}", t.Name, keyName, id);
            _connection.Execute(sql);
        }





        public IEnumerable<T> QueryList<T>()
        {
            var type = typeof(T);
            string sql = string.Format("select * from {0}", type.Name);
            return _connection.Query<T>(sql);
        }

        public IEnumerable<T> QueryList<T>(int pageSize, int page)
        {
            var type = typeof(T);
            string sql = string.Format("select * from {0}", type.Name);
            string querySql = string.Format(@"WITH OrderedEmployees AS ({0})
                                        SELECT *
                                    FROM OrderedEmployees
                                    WHERE RowNum BETWEEN (" + pageSize + " * (" + page + " - 1) + 1) AND (" + pageSize + " * " + page + ");", sql);
            return _connection.Query<T>(querySql);
        }

        public IEnumerable<T> QueryList<T>(string where)
        {
            var type = typeof(T);
            string sql = string.Format("select * from {0} where 1=1 {1}", type.Name, where);
            return _connection.Query<T>(sql);
        }

        public IEnumerable<T> QueryList<T>(string where, int pageSize, int page)
        {
            var type = typeof(T);
            string sql = string.Format("select * from {0} where 1=1 {1}", type.Name, where);
            string querySql = string.Format(@"WITH OrderedEmployees AS ({0})
                                        SELECT *
                                    FROM OrderedEmployees
                                    WHERE RowNum BETWEEN (" + pageSize + " * (" + page + " - 1) + 1) AND (" + pageSize + " * " + page + ");", sql);
            return _connection.Query<T>(querySql);
        }

        public T QueryFirst<T>(string id, string keyName = "id")
        {
            var type = typeof(T);
            string sql = string.Format("select * from {0} where {1}={2}", type.Name, keyName, id);
            return _connection.QueryFirst<T>(sql);
        }


        public T QueryFirstWhere<T>(string where)
        {
            var type = typeof(T);
            string sql = string.Format("select * from {0} where 1=1 {1}", type.Name, where);
            return _connection.QueryFirst<T>(sql);
        }

        public IEnumerable<T> QuerySql<T>(string sql, object param = null)
        {
            return _connection.Query<T>(sql, param);
        }


        public IEnumerable<T> QuerySql<T>(string sql, int pageSize, int page, object param = null)
        {
            string querySql = string.Format(@"WITH OrderedEmployees AS ({0})
                                        SELECT *
                                    FROM OrderedEmployees
                                    WHERE RowNum BETWEEN (" + pageSize + " * (" + page + " - 1) + 1) AND (" + pageSize + " * " + page + ");", sql);
            return _connection.Query<T>(querySql, param);
        }


        public void CreateTable<T>()
        {

        }

        public int GetCount<T>(string where = "")
        {
            throw new NotImplementedException();
        }

        public IEnumerable<T> QueryList<T>(Expression<Func<T, bool>> expression)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<T> QueryList<T>(Expression<Func<T, bool>> expression, int pageSize, int page)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<TResult> QueryJoin<T, TJoin, TResult>(Expression<Func<T, TJoin, bool>> joinExpression, Expression<Func<T, TJoin, TResult>> selectExpression, Expression<Func<T, TJoin, bool>> whereExpression = null)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<TResult> QueryJoin<T, TJoin, TResult>(Expression<Func<T, TJoin, bool>> joinExpression, Expression<Func<T, TJoin, TResult>> selectExpression, Expression<Func<T, TJoin, bool>> whereExpression, int pageSize, int page)
        {
            throw new NotImplementedException();
        }

        public T QueryFirstWhere<T>(Expression<Func<T, bool>> expression)
        {
            throw new NotImplementedException();
        }

        public void Update<T>(Expression<Func<T, T>> setExpression, Expression<Func<T, bool>> whereExpression, DbTransaction transaction = null)
        {
            throw new NotImplementedException();
        }

        public void Update<T>(Expression<Func<T, T>> setExpression, string id, string keyName = "Id", DbTransaction transaction = null)
        {
            throw new NotImplementedException();
        }

        public void BatchInsert<T>(IEnumerable<T> entities, DbTransaction transaction = null)
        {
            throw new NotImplementedException();
        }

        public void BatchUpdate<T>(IEnumerable<T> entities, Expression<Func<T, T>> setExpression, Expression<Func<T, bool>> whereExpression, DbTransaction transaction = null)
        {
            throw new NotImplementedException();
        }

        public void BatchUpdate<T>(IEnumerable<T> entities, string keyName = "Id", DbTransaction transaction = null)
        {
            throw new NotImplementedException();
        }

        public T QuerySqlOne<T>(string sql, object param = null)
        {
            throw new NotImplementedException();
        }

        public void delete<T>(Expression<Func<T, bool>> expression, DbTransaction transaction = null)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<T> QueryList<T>(int pageSize, int page, string orderby = "")
        {
            throw new NotImplementedException();
        }

        public void Execute(string sql, object param = null, System.Data.IDbTransaction transaction = null)
        {
            throw new NotImplementedException();
        }

        public TableInfo GetTableInfo(string tableName)
        {
            throw new NotImplementedException();
        }

        public List<string> GetTbNameList()
        {
            throw new NotImplementedException();
        }

        public System.Data.ConnectionState connectState()
        {
            throw new NotImplementedException();
        }

        public Task insertAsync<T>(T entity, DbTransaction transaction = null)
        {
            throw new NotImplementedException();
        }
    }
}
